package Presenter;

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; 





import Model.Game;
import Model.Mule;
import Model.Player;
import Model.Tile;

/**
 * Handles loading and saving from the database.
 * @author Matt Miller
 *
 */
public class DatabaseTransactions {
	
	
	
	/**
	 * Establishes a connection with the database
	 * @return the connection
	 */
	private static Connection createConnection() {
		Connection con = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://academic-"
					+ "mysql.cc.gatech.edu/cs4400_Group_47", "cs4400_Group_47",
					"eOvj27cQ");
			if(!con.isClosed())
				System.out.println("Successfully connected to " +
					"MySQL server using TCP/IP...");
		
		} catch(ClassNotFoundException e) {
				System.out.println("threw a class not found exception");
			
		} catch(SQLException p) {
				System.out.println(p.getMessage());
		} catch (InstantiationException e) {
			System.out.println("threw an InstantiationException error");

		} catch (IllegalAccessException e) {
			System.out.println("threw an illegal access exception");
		}
		finally {
		}
		return con;
	}
	
	/**
	 * Saves the game into the database
	 * @param g
	 * @param r
	 * @param name
	 * @throws SQLException
	 */
	public static void saveGame(Game g, Round r, String name) throws SQLException {
		Connection dbConnection = createConnection();
		try{
		System.out.println("Made it here");
		PreparedStatement saveStatement = dbConnection.prepareStatement("INSERT"
				+ " INTO GAMERECORDMSTR(game_name) VALUES(?)");
		System.out.println("created the saveStatement");
		saveStatement.setString(1, name);
		saveStatement.executeUpdate();
		System.out.println("Made it to commit");
		
		PreparedStatement getGID = dbConnection.prepareStatement("select gid from GAMERECORDMSTR "
				+ "where game_name like ?");
		getGID.setString(1,name + "%");
		
		ResultSet rs = getGID.executeQuery();
		int gid = 0;
		while(rs.next()) {
			gid = rs.getInt("gid");
		}
		
		PreparedStatement saveGameStatement = dbConnection.prepareStatement("INSERT"
				+ " INTO GAMEDETL(gid, difficulty , mapType, playerCount, humanPlayerCount,"
				+ " currentRound, totalRounds) VALUES(?, ?,?,?,?,?,?)");
		
		saveGameStatement.setInt(1, gid);
		saveGameStatement.setString(2,g.getDifficultyString());
		saveGameStatement.setString(3, g.getMapTypeString());
		saveGameStatement.setInt(4, g.getPlayerCount());
		saveGameStatement.setInt(5, g.getHumanPlayerCount());
		saveGameStatement.setInt(6, g.getCurrentRound());
		saveGameStatement.setInt(7, g.getTotalRounds());
		
		PreparedStatement saveTurnStatement = dbConnection.prepareStatement("INSERT"
				+ " INTO TURN VALUES(?,?,?)");
		
		saveTurnStatement.setInt(1, gid);
		saveTurnStatement.setInt(2, r.getTurn().getCurrentPlayer());
		saveTurnStatement.setBoolean(3, r.getTurn().landBought());
		
		saveGameStatement.executeUpdate();
		saveTurnStatement.executeUpdate();
		for(int i = 0; i<g.getMap().getTileArray().length; i++) {
			Tile currentTile = g.getMap().getTileArray()[i];
			PreparedStatement saveTileStatement = dbConnection.prepareStatement("INSERT"
					+ " into TILE VALUES(?,?,?,?,?,?)");
			saveTileStatement.setInt(1,i);
			if(currentTile.getOwner() == null) {
				saveTileStatement.setString(2,"");
			} else {
				saveTileStatement.setString(2,currentTile.getOwner().getName());
			}
			saveTileStatement.setString(3, currentTile.getType());
			saveTileStatement.setBoolean(4,currentTile.getMuleHere());
			if(currentTile.getMuleHere()) {
				saveTileStatement.setString(5, currentTile.getMule().getType());
			} else {
				saveTileStatement.setString(5, null);
			}
			saveTileStatement.setInt(6, gid);
			
			saveTileStatement.executeUpdate();
		}
		Player[] players = g.getPlayers();
		for(int j = 0; j<4; j++) {
			Player currentPlayer = players[j];
			PreparedStatement savePlayerStatement = dbConnection.prepareStatement("INSERT"
					+ " INTO PLAYER VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
			savePlayerStatement.setInt(1,gid);
			savePlayerStatement.setString(2,currentPlayer.getRace());
			savePlayerStatement.setString(3, currentPlayer.getColor());
			savePlayerStatement.setInt(4, currentPlayer.getMoney());
			savePlayerStatement.setInt(5,currentPlayer.getFood());
			savePlayerStatement.setInt(6,currentPlayer.getEnergy());
			savePlayerStatement.setInt(7,currentPlayer.getOre());
			savePlayerStatement.setString(8,currentPlayer.getName());
			savePlayerStatement.setBoolean(9,currentPlayer.getPossessMule());
			savePlayerStatement.setBoolean(10,currentPlayer.isHuman());
			savePlayerStatement.setInt(11,currentPlayer.getScore());
			if(currentPlayer.getPossessMule()) {
				savePlayerStatement.setString(12,currentPlayer.getMule().getType());
			} else {
				savePlayerStatement.setString(12,"");
			}
			savePlayerStatement.execute();
			
		}
		
		saveStatement.close();
		
		}	finally {
            if (dbConnection != null) dbConnection.close();
        	}
		
	}

	/**
	 * Loads the game from the database
	 * @param game
	 * @param round
	 * @param gameName
	 * @throws SQLException
	 */
	public static void loadGame(Game game, Round round, String gameName) throws SQLException {
		System.out.println("does it even get in here?");
		Connection dbConnection = createConnection();
		try{
			System.out.println("trying to get the GID");
			PreparedStatement getGID = dbConnection.prepareStatement("select gid from GAMERECORDMSTR "
					+ "where game_name like ?");
			getGID.setString(1,gameName + "%");
			
			ResultSet rs = getGID.executeQuery();
			int gid = 0;
			while(rs.next()) {
				gid = rs.getInt("gid");
			}
			System.out.println("GID is "+ gid);
			PreparedStatement getGameInfo = dbConnection.prepareStatement("SELECT "
					+ "difficulty, maptype, playerCount, humanPlayerCount, currentRound,"
					+ " totalRounds from GAMEDETL where gid = ?");
			getGameInfo.setInt(1, gid);
			rs = getGameInfo.executeQuery();
			String difficulty = null;
			String maptype = null;
			int playerCount = 0;
			int humanPlayerCount = 0;
			int currentRound = 0;
			int totalRounds = 0;
			while(rs.next()) {
				difficulty = rs.getString("difficulty");
				maptype = rs.getString("maptype");
				playerCount = rs.getInt("playerCount");
				humanPlayerCount = rs.getInt("humanPlayerCount");
				currentRound = rs.getInt("currentRound");
				totalRounds = rs.getInt("totalRounds");
			}
			game.setCurrentRound(currentRound);
			game.setDifficultyString(difficulty);
			game.setMapTypeString(maptype);
			game.setPlayerCount(playerCount);
			game.setHumanPlayerCount(humanPlayerCount);
			game.setTotalRounds(totalRounds);
			System.out.println("got the Game info");
			PreparedStatement getTurnInfo = dbConnection.prepareStatement("Select "
					+ "currentPlayer,landbought from TURN where gid = ?");
			getTurnInfo.setInt(1, gid);
			int currentPlayer = 0;
			boolean landbought = false;
			rs = getTurnInfo.executeQuery();
			while(rs.next()) {
				landbought = rs.getBoolean("landbought");
				currentPlayer = rs.getInt("currentPlayer");
			}
			round.getTurn().setCurrentPlayer(currentPlayer);
			round.getTurn().setLandBought(landbought);
			System.out.println("got the Round");
			
			PreparedStatement getPlayerInfo = dbConnection.prepareStatement("SELECT "
					+ "race, color, money, food, energy, ore, name, possessMule, isHuman, score, "
					+ "MID from PLAYER where gid = ?");
			getPlayerInfo.setInt(1, gid);
			rs = getPlayerInfo.executeQuery();
			String race = null;
			String color = null;
			int money = 0;
			int food = 0;
			int energy = 0;
			int ore = 0;
			String name = null;
			boolean possessMule = false;
			boolean isHuman = false;
			int score = 0;
			String MID = null;
			Player[] loadPlayers = new Player[4];
			int index = 0;
			while(rs.next()){
				race = rs.getString("race");
				color = rs.getString("color");
				money = rs.getInt("money");
				food = rs.getInt("food");
				energy = rs.getInt("energy");
				ore = rs.getInt("ore");
				name = rs.getString("name");
				MID=rs.getString("MID");
				score = rs.getInt("score");
				Mule mule = null;
				if(!MID.equals("")){
					mule = new Mule(MID);
				}
				
				loadPlayers[index] = new Player(race, color, money, food, energy, ore, 
						name, possessMule, isHuman, score, mule);
				index++;
			}
			game.setPlayers(loadPlayers);
			round.orderPlayers();
			System.out.println("got the players");
			
			PreparedStatement getTileInfo = dbConnection.prepareStatement("SELECT tnum, "
					+ "owner, landType, muleHere, MID from TILE where gid = ?");
			getTileInfo.setInt(1, gid);
			int tnum = 0;
			Player owner = null;
			String pname = null;
			//String landType = null;
			boolean muleHere = false;
			String tileMID = null;
			Tile[] tiles = game.getMap().getTileArray();
			rs = getTileInfo.executeQuery();
			while(rs.next()){
				for(int i = 0;i<tiles.length;i++){
					tnum = rs.getInt("tnum");
					pname = rs.getString("owner");
					owner=findPlayer(loadPlayers,pname);
					//landType = rs.getString("landType");
					muleHere = rs.getBoolean("muleHere");
					Mule newMule = null;
					if(muleHere) {
						tileMID = rs.getString("MID");
						newMule = new Mule(tileMID);
					}
					tiles[tnum].setMule(newMule);
					tiles[tnum].setMuleHere(muleHere);
					tiles[tnum].setOwner(owner);
				}
			}
			
			System.out.println("got the tiles");
		}
		finally {
            if (dbConnection != null) dbConnection.close();
        	}
		
	}

	/**
	 * Locates a player within an array
	 * @param loadPlayers
	 * @param name
	 * @return
	 */
	private static Player findPlayer(Player[] loadPlayers, String name) {
		for(int i = 0;i<loadPlayers.length;i++) {
			if(loadPlayers[i].getName().equals(name)) {
				return loadPlayers[i];
			}
		}
		return null;
	}
	
}
